package com.egao.common.module.system.transfer;

import com.egao.common.core.web.JsonResult;
import com.egao.common.module.company.entity.Company;
import com.egao.common.module.company.entity.Mutual;
import com.egao.common.module.company.entity.MutualApply;
import com.egao.common.module.company.entity.PreachCloud;
import com.egao.common.module.company.service.CompanyService;
import com.egao.common.module.company.service.MutualApplyService;
import com.egao.common.module.company.service.MutualService;
import com.egao.common.module.company.service.PreachCloudService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 单位数据导入
 * @author: dl
 */
@RestController
@Slf4j
public class MutualDataController {

    @Value("${old.datasource.jyurl}")
    private String url;

    @Value("${old.datasource.username}")
    private String userName;

    @Value("${old.datasource.password}")
    private String password;

    @Value("${minio.old-data-head}")
    private String contentUrl;

    @Autowired
    private CompanyService companyService;

    @Autowired
    private MutualService mutualService;

    @Autowired
    private MutualApplyService mutualApplyService;

    @Transactional(rollbackFor = Exception.class)
    public JsonResult transfer() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        long beginTime = System.currentTimeMillis();
        List<Company> companyList = getCompanyList();
        try {
            List<MutualApply> mutualApplyList = new ArrayList<>();
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, password);
            st = conn.createStatement();
            String applySql = "SELECT t1.id, " +
                    " t1.doublechooseid as mutualId," +
                    "  t1.companyid as companyId," +
                    "  t1.num," +
                    "  t1.checkstate," +
                    "  t1.checkcomment," +
                    "  t1.cancel," +
                    "  t1.create_date," +
                    "  t1.update_date," +
                    "  t1.del_flag," +
                    "  t2.title," +
                    "  t2.work_type," +
                    "  t2.valid_time," +
                    "  t2.job_name," +
                    "  t2.number," +
                    "  t2.work_place," +
                    "  t2.major," +
                    "  t2.education," +
                    "  t2.hits," +
                    "  t2.link_man," +
                    "  t2.link_mobile," +
                    "  t2.email," +
                    "  t2.recruit_url," +
                    "  t2.`comment`" +
                    " FROM " +
                    " doublecompany t1" +
                    " LEFT JOIN double_company_recruit t2 ON t1.recruitid = t2.id" +
                    " WHERE" +
                    " t1.doublechooseid IS NOT NULL";
            rs = st.executeQuery(applySql);
            while (rs.next()) {
                String oldId = rs.getString("id");
                String oldMutualId = rs.getString("mutualId");
                String companyId = rs.getString("companyId");
                String title = rs.getString("title");
                Integer num = rs.getInt("num");

                Date createDate = rs.getTimestamp("create_date") != null ? new Date(rs.getTimestamp("create_date").getTime()) : null;
                Date updateDate = rs.getTimestamp("update_date") != null ? new Date(rs.getTimestamp("update_date").getTime()) : null;
                Date validTime = rs.getTimestamp("valid_time") != null ? new Date(rs.getTimestamp("valid_time").getTime()) : null;

                String workType = rs.getString("work_type");
                String jobName = rs.getString("job_name");
                String number = rs.getString("number");
                String workPlace = rs.getString("work_place");
                String major = rs.getString("major");
                String education = rs.getString("education");
                Integer hits = rs.getInt("hits");

                String linkMan = rs.getString("link_man");
                String linkMobile = rs.getString("link_mobile");
                String email = rs.getString("email");
                String recruitUrl = rs.getString("recruit_url");
                String comment = rs.getString("comment");
                String delFlag = rs.getString("del_flag");
                Integer cancel = rs.getInt("cancel");
                Integer checkstate = rs.getInt("checkstate");
                String checkcomment = rs.getString("checkcomment");
                MutualApply mutualApply = new MutualApply();
                mutualApply.setOldId(oldId);
                mutualApply.setOldMutualId(oldMutualId);
                if (companyId != null) {
                    Company company = getCompanyId(companyList, companyId);
                    if (company != null) {
                        mutualApply.setCompanyId(company.getId());
                    } else {
                        mutualApply.setCompanyId(0);
                    }
                }
                mutualApply.setNumberPlate(num);
                mutualApply.setCreateTime(createDate);
                mutualApply.setUpdateTime(updateDate);
                mutualApply.setTitle(title);
                mutualApply.setJobCategory(workType);
                mutualApply.setValidTime(validTime);
                mutualApply.setJobName(jobName);
                mutualApply.setNumber(number);
                mutualApply.setArea(workPlace);
                mutualApply.setMajors(major);
                mutualApply.setEducation(education);
                mutualApply.setHits(hits);
                mutualApply.setRecruitContact(linkMan);
                mutualApply.setRecruitMobile(linkMobile);
                mutualApply.setEmail(email);
                mutualApply.setRecruitWebsite(recruitUrl);
                mutualApply.setContent(comment);
                mutualApply.setContent(comment != null ? comment.replaceAll("/ueditor/jsp/upload", contentUrl + "/ueditor/jsp/upload") : "");
                mutualApply.setDeleted("0".equals(delFlag) ? 0 : 1);
                mutualApply.setState(getState(checkstate));
                mutualApply.setStateContent(checkcomment);
                mutualApply.setInvalid(cancel);
                mutualApplyList.add(mutualApply);
            }

            String sql = "SELECT" +
                    "  ID" +
                    ", DoubleType" +
                    ", Title" +
                    ", Sponsor" +
                    ", HoldDate" +
                    ", HolddateInt" +
                    ", HoldTime" +
                    ", FieldID" +
                    ", FieldName" +
                    ", Comment" +
                    ", EndTime" +
                    ", IsOpen" +
                    ", Number" +
                    ", Cancel" +
                    ", create_date" +
                    ", del_flag" +
                    ", create_by" +
                    ", update_by" +
                    ", update_date" +
                    ", remarks" +
                    ", hits" +
                    ", informationtype" +
                    " FROM doublechoose WHERE informationtype = 1";

            rs = st.executeQuery(sql);
            log.info("总数：{}", rs.getRow());
            int i = 0;
            while (rs.next()) {
                String oldId = rs.getString("ID");
                String title = rs.getString("Title");
                String companyName = rs.getString("Sponsor");

                Date holddate = rs.getTimestamp("HoldDate") != null ? new Date(rs.getTimestamp("HoldDate").getTime()) : null;
                Integer HoldTime = rs.getInt("HoldTime");
                String comment = rs.getString("Comment");
                String FieldName = rs.getString("FieldName");
                Date EndTime = rs.getTimestamp("EndTime") != null ? new Date(rs.getTimestamp("EndTime").getTime()) : null;

                String del_flag = rs.getString("del_flag");


                Integer IsOpen = rs.getInt("IsOpen");
                Integer Number = rs.getInt("Number");
                Integer Cancel = rs.getInt("Cancel");
                Integer hits = rs.getInt("hits");

                Date createDate = rs.getTimestamp("create_date") != null ? new Date(rs.getTimestamp("create_date").getTime()) : null;
                Date updateDate = rs.getTimestamp("update_date") != null ? new Date(rs.getTimestamp("update_date").getTime()) : null;

                Mutual mutual = new Mutual();
                mutual.setOldId(oldId);
                mutual.setTitle(title);
                mutual.setCompanyName(companyName);
                mutual.setTimeType(String.valueOf(HoldTime));
                mutual.setStartTime(holddate);
                mutual.setEndTime(EndTime);
                mutual.setContent(comment != null ? comment.replaceAll("/ueditor/jsp/upload", contentUrl + "/ueditor/jsp/upload") : "");
                mutual.setFieldName(FieldName);
                mutual.setDeleted("0".equals(del_flag) ? 0 : 1);
                mutual.setOpenFlag(String.valueOf(IsOpen));
                mutual.setCompanyNumber(Number);
                mutual.setHits(hits);
                mutual.setInvalid(Cancel);
                mutual.setCreateTime(createDate);
                mutual.setUpdateTime(updateDate);


                i++;
                log.info("校内双选会信息：{} 数量：{} 个", title, i);
                if (mutualService.save(mutual)) {

                    List<MutualApply> mutualApplies = new ArrayList<>();
                    for (MutualApply mutualApply : mutualApplyList) {
                        if (mutual.getOldId().equals(mutualApply.getOldMutualId())) {
                            mutualApply.setMutualId(mutual.getId());
                            mutualApplies.add(mutualApply);
                        }
                    }
                    if (mutualApplies.size() > 0) {
                        mutualApplyService.saveBatch(mutualApplies);
                    }
                    log.info("校内双选会信息：{} 数量：{} 个", title, i);
                }
            }
            long overTime = System.currentTimeMillis();
            long finishTime = (overTime - beginTime) / 1000;
            log.info("完成时间 : {} 分", finishTime);
            return JsonResult.ok(String.format("完成时间 : %s 分", finishTime));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return JsonResult.ok();
    }


    /**
     * 查询所有单位信息
     *
     * @return
     */
    private List<Company> getCompanyList() {
        return companyService.list();
    }

    /**
     * 查询原始
     *
     * @return
     */
    private Company getCompanyId(List<Company> companyList, String oldId) {
        for (Company company : companyList) {
            if (oldId.equals(company.getOldId())) {
                return company;
            }
        }
        return null;
    }

    private String getState(Integer state) {
        switch (state) {
            case 0:
                return "0";
            case 1:
                return "1";
            case -1:
                return "2";
            case -2:
                return "3";
            default:
                return "";
        }
    }

}
